#安装方法
#pip install SQLAlchemy    -i https://pypi.douban.com/simple
#  pip install mysqlclient   -i https://pypi.douban.com/simple
from datetime import datetime
import json

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy import * #Column, Integer, String ,DateTime, Boolean
#from retrying import retry
# 连接数据库
# engine = create_engine('mysql://root:pass@cdb-9bdie3jb.gz.tencentcdb.com:1011/AiTrade?charset=utf8')
#engine = create_engine("excel:///?Excel File='C:/MyExcelWorkbooks/SampleWorkbook.xlsx'")
engine = create_engine('sqlite:///foo.db?check_same_thread=False')
## 编码问题
# # 获取基类
Base = declarative_base()

## 新增数据
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine,expire_on_commit=False)
timeForm="%Y-%m-%d %H:%M:%S"


class SignRecord(Base): # 继承基类
    __tablename__ = 'SignRecord'
    id = Column(Integer, primary_key = True)
    name = Column(String(20), nullable = False)  
    create_time= Column(DateTime)
 



class OrmSignRecord(object):
    def __init__(self):
        self.session = Session()
        SignRecord.metadata.create_all(engine)
        self.session.close()


    def InitTable(self):
        SignRecord.metadata.create_all(engine)   # 创建表格
        self.session.close()
    #@retry
    def add_one(self,new_obj):
        self.session = Session()
        new_obj.create_time=datetime.now()
        self.session.add(new_obj)
        self.session.commit()
        self.session.close()
        return new_obj

    ## 查询数据        
    def get_one(self,id):
        return self.session.query(SignRecord).get(id)  # get 是选id为几的

    def get_byName(self,name):
        list1= self.session.query(SignRecord).filter(SignRecord.name==name,SignRecord.id>0).order_by(SignRecord.create_time.asc())#.all()
        if list1.count()>=1:
            return list1
        else:
            return null

    def get_count(self):
        return self.session.query(SignRecord).count()  # get 是选id为几的

    def get_all(self):
        return self.session.query(SignRecord).all()



class CollRecord(Base): # 继承基类
    __tablename__ = 'CollRecord'
    id = Column(Integer, primary_key = True)
    code = Column(String(20), nullable = False)  
    name = Column(String(20), nullable = True)  
    depart = Column(String(20), nullable = True)  
    remark = Column(String(200), nullable = True)  
    create_time= Column(DateTime)
    update_time= Column(DateTime)
    score1=Column(Integer, nullable = True)
    score2=Column(Integer, nullable = True)
    score3=Column(Integer, nullable = True)
    score4=Column(Integer, nullable = True)
    def to_json(self):
        dict = self.__dict__
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict  



class OrmCollRecord(object):
    def __init__(self):
        self.session = Session()
        SignRecord.metadata.create_all(engine)
        self.session.close()


    def InitTable(self):
        CollRecord.metadata.create_all(engine)   # 创建表格
    #@retry
    def add_one(self,new_obj):
        self.session = Session()
        new_obj.create_time=datetime.now()
        self.session.add(new_obj)
        self.session.commit()
        self.session.close()
        return new_obj

    def update_one(self,new_obj):
        self.session = Session()
        # self.session.query(CollRecord).get(new_obj.id).update({name:'张三'})
        list1=self.session.query(CollRecord).filter(CollRecord.code==new_obj.code)#.all()
        if list1.count()==1:
            u=list1[0]
            u.update_time=datetime.now()
            u.name=new_obj.name
            u.depart=new_obj.depart
            u.remark=new_obj.remark
            u.score1=new_obj.score1
            u.score2=new_obj.score2
            u.score3=new_obj.score3
            u.score4=new_obj.score4
        else:
            u=CollRecord()
            u.create_time=datetime.now()
            u.update_time=datetime.now()
            u.name=new_obj.name
            u.depart=new_obj.depart
            u.remark=new_obj.remark
            u.score1=new_obj.score1
            u.score2=new_obj.score2
            u.score3=new_obj.score3
            u.score4=new_obj.score4
            u.code=new_obj.code
            self.session.add(u)
     
        self.session.commit()
        self.session.close()
        return new_obj

    ## 查询数据        
    def get_one(self,id):
        return self.session.query(CollRecord).get(id)  # get 是选id为几的

    def get_departs(self):
        list1=self.session.query(CollRecord.depart).distinct().all()
        return list1  # get 是选id为几的

    def get_byCode(self,code):
        list1= self.session.query(CollRecord).filter(CollRecord.code==code)#.all()
        if list1.count()>=1:
            return list1[0]
        else:
            return null


   